IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ClientAccountSnapshotFetch')
	BEGIN
		DROP  Procedure  ClientAccountSnapshotFetch
	END

GO

CREATE Procedure ClientAccountSnapshotFetch
(
	@accountId int,
	@relativeInvoiceId int,
	@snapshotDate datetime
)
AS

SELECT a.AccountId, 
	(
		dbo.FN_CleanMoneyValue(ci.Balance) +
		dbo.FN_CleanMoneyValue(p.Payments) +
		dbo.FN_CleanMoneyValue(c.Credits)
	) AS PreviousBalance,
	dbo.FN_CleanMoneyValue(ciToday.Balance) AS InvoicesToday,
	dbo.FN_CleanMoneyValue(pToday.Payments) AS PaymentsToday,
	dbo.FN_CleanMoneyValue(cToday.Credits) AS CreditsToday,
	dbo.FN_CleanMoneyValue(ciExclusive.Balance) AS InvoicesExclusive
FROM Accounts AS a
INNER JOIN Invoices AS i
	ON i.AccountId = a.AccountId

-- Client invoices total (excluding relative invoice).
LEFT JOIN (
	SELECT SUM(InvoiceItems.Amount) AS Balance, Invoices.AccountId
	FROM Invoices
	INNER JOIN (
		SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
		FROM InvoiceItems
		GROUP BY InvoiceItemId, InvoiceId
	) AS InvoiceItems ON InvoiceItems.InvoiceId = Invoices.InvoiceId
	AND Invoices.TypeFlag = 1
	AND Invoices.InvoiceId != @relativeInvoiceId
	AND InvoiceDate = CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY Invoices.AccountId
) AS ciExclusive
	ON ciExclusive.AccountId = a.AccountId

-- Client invoices before snapshot date.
LEFT JOIN (
	SELECT SUM(InvoiceItems.Amount) AS Balance, Invoices.AccountId
	FROM Invoices
	INNER JOIN (
		SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
		FROM InvoiceItems
		GROUP BY InvoiceItemId, InvoiceId
	) AS InvoiceItems ON InvoiceItems.InvoiceId = Invoices.InvoiceId
	AND Invoices.TypeFlag = 1
	AND Invoices.InvoiceId != @relativeInvoiceId
	AND InvoiceDate < CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY Invoices.AccountId
) AS ci
	ON ci.AccountId = a.AccountId

-- Client invoices on snapshot date (where not current invoice).
LEFT JOIN (
	SELECT SUM(InvoiceItems.Amount) AS Balance, Invoices.AccountId
	FROM Invoices
	INNER JOIN (
		SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
		FROM InvoiceItems
		GROUP BY InvoiceItemId, InvoiceId
	) AS InvoiceItems ON InvoiceItems.InvoiceId = Invoices.InvoiceId
	AND Invoices.TypeFlag = 1
	AND Invoices.InvoiceId != @relativeInvoiceId
	AND InvoiceDate >= CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY Invoices.AccountId
) AS ciToday
	ON ciToday.AccountId = a.AccountId

-- Payments made before snapshot date.
LEFT JOIN (
	SELECT SUM(Amount) AS Payments, AccountId
	FROM Payments
	WHERE PaymentDate < CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY AccountId
) AS p ON p.AccountId = a.AccountId

-- Payments made on snapshot date.
LEFT JOIN (
	SELECT SUM(Amount) AS Payments, AccountId
	FROM Payments
	WHERE PaymentDate >= CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY AccountId
) AS pToday ON pToday.AccountId = a.AccountId

-- Credits made before snapshot date.
LEFT JOIN (
	SELECT SUM(Amount) AS Credits, AccountId
	FROM Credits
	WHERE CreditDate < CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY AccountId
) AS c ON c.AccountId = a.AccountId

-- Credits made on snapshot date.
LEFT JOIN (
	SELECT SUM(Amount) AS Credits, AccountId
	FROM Credits
	WHERE CreditDate >= CONVERT(CHAR(8),@snapshotDate,10)
	GROUP BY AccountId
) AS cToday ON cToday.AccountId = a.AccountId

WHERE a.AccountId = @accountId

GO